package sqlStroage

import (
	"database/sql"
	"errors"
	"fmt"
	"gitee.com/kinwyb/appTools/common"
	"gitee.com/kinwyb/conv"
	"gitee.com/kinwyb/godb"
	"gitee.com/kinwyb/workflow/public"
	"gitee.com/kinwyb/workflow/storage"
	"time"
)

type sqlStroage struct {
	conn godb.SQL
}

var sqlConnErr = errors.New("数据库连接错误")
var ctxNullErr = errors.New("上下文数据空")

func InitMySQLStorage(host, username, password, db string) error {
	mc, err := godb.MYSQLConnect(host, username, password, db)
	if err != nil {
		return err
	}
	storage.RegisterStorage(&sqlStroage{
		conn: mc,
	})
	return nil
}

func checkContext(ctx *public.ContextValue) bool {
	if ctx == nil || ctx.UserName == "" || ctx.UserID == "" || ctx.OrgID == "" {
		return false
	}
	return true
}

func (m *sqlStroage) FlowSave(nodes []storage.Node, routeNodes []storage.RouteNode, ctx *public.ContextValue) error {
	if m.conn == nil {
		return sqlConnErr
	}
	if len(nodes) < 2 {
		return errors.New("空数据流")
	}
	if !checkContext(ctx) {
		return ctxNullErr
	}
	return m.conn.Transaction(func(tx godb.TxSQL) error {
		timnow := timeNow()
		nodeInsertSQL := "INSERT INTO FlowNode(TopNodeID, NodeID, NodeCode, NodeName, NodeLevel, NodeType, UserGroupID, RouteID, RouteSuccID, RouteFailID, ChildID, CreateUser, CreateTime,CreateUserID, OrgID) " +
			" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "
		var args = make([]any, 16)
		for _, node := range nodes {
			args[0] = node.TopNodeID
			args[1] = node.NodeID
			args[2] = node.NodeCode
			args[3] = node.NodeName
			args[4] = node.NodeLevel
			args[5] = node.NodeType
			args[6] = node.UserGroupID
			args[7] = node.RouteID
			args[8] = node.RouteSuccID
			args[9] = node.RouteFailID
			args[10] = node.ChildID
			args[11] = ctx.UserName
			args[12] = timnow
			args[13] = ctx.UserID
			args[14] = ctx.OrgID
			err := tx.Exec(nodeInsertSQL, args...).Error()
			if err != nil {
				return err
			}
		}
		if len(routeNodes) > 0 {
			routeNodeInsertSQL := "INSERT INTO RouteNode(TopID, RouteID, ParentID, Formula, ActType) VALUES (?, ?, ?, ?, ?)"
			for _, v := range routeNodes {
				err := tx.Exec(routeNodeInsertSQL, v.TopID, v.RouteID, v.ParentID, v.Formula, v.ActType).Error()
				if err != nil {
					return err
				}
			}
		}
		return nil
	})
}

func (m *sqlStroage) FlowQuery(flowID string) ([]storage.Node, error) {
	if m.conn == nil {
		return nil, sqlConnErr
	}
	sqlString := "SELECT * FROM FlowNode WHERE TopNodeID = ? ORDER BY NodeLevel"
	res := m.conn.QueryRows(sqlString, flowID)
	return godb.QueryResultToStructArray[storage.Node](res)
}

func (m *sqlStroage) RouteQuery(routeID string) ([]storage.RouteNode, error) {
	if m.conn == nil {
		return nil, sqlConnErr
	}
	sqlString := "SELECT * FROM RouteNode WHERE TopID = ?"
	res := m.conn.QueryRows(sqlString, routeID)
	return godb.QueryResultToStructArray[storage.RouteNode](res)
}

func (m *sqlStroage) WorkCreate(work storage.Work, detail []storage.WorkDetail, ctx *public.ContextValue) error {
	if m.conn == nil {
		return sqlConnErr
	}
	if !checkContext(ctx) {
		return ctxNullErr
	}
	return m.conn.Transaction(func(tx godb.TxSQL) error {
		workInsertSQL := `
INSERT INTO Work(WorkID, WorkFlowID, WorkFlowCode, WorkName, WorkStatus,
                 ExtendVal1, ExtendVal2, ExtendVal3, ExtendVal4, ExtendVal5, 
                 Data, CreateUser, CreateTime, CreateUserID,OrgID) VALUES (?, ?, ?, ?, 0,
                 ?, ?, ?, ?, ?, ?, ?, ?,?,?)
`
		err := tx.Exec(workInsertSQL, work.WorkID, work.WorkFlowID, work.WorkFlowCode, work.WorkName,
			work.ExtendVal1, work.ExtendVal2, work.ExtendVal3, work.ExtendVal4, work.ExtendVal5,
			work.Data, ctx.UserName, timeNow(), ctx.UserID, ctx.OrgID).Error()
		if err != nil {
			return err
		}
		detailSet := "INSERT INTO WorkDetail(WorkDetailID, WorkID, NodeID, WorkDetailStatus, UpdateUser, UpdateTime) VALUES (?, ?, ?, ?, ?, ?)"
		for _, v := range detail {
			updateUser := sql.NullString{}
			if v.UpdateUser != "" {
				updateUser.Scan(v.UpdateUser)
			}
			updateTime := sql.NullString{}
			if v.UpdateTime != "" {
				updateTime.Scan(v.UpdateTime)
			}
			err = tx.Exec(detailSet, v.WorkDetailID, v.WorkID, v.NodeID, v.WorkDetailStatus, updateUser, updateTime).Error()
			if err != nil {
				return err
			}
		}
		return nil
	})
}

func (m *sqlStroage) WorkFlowSave(workFlow storage.WorkFlow, ctx *public.ContextValue) error {
	if m.conn == nil {
		return sqlConnErr
	}
	if !checkContext(ctx) {
		return ctxNullErr
	}
	insertSQL := "INSERT INTO WorkFlow(WorkFlowID, WorkFlowCode, WorkFlowName, FlowID, CreateUser, CreateTime,CreateUserID,OrgID) VALUES (?, ?, ?, ?, ?, ?,?,?)"
	err := m.conn.Exec(insertSQL, workFlow.WorkFlowID, workFlow.WorkFlowCode, workFlow.WorkFlowName, workFlow.FlowID, ctx.UserName, timeNow(), ctx.UserID, ctx.OrgID).Error()
	if err != nil {
		return err
	}
	return nil
}

func (m *sqlStroage) WorkFlowQuery(workFlowID string, workFlowCode string, useStatus int, ctx *public.ContextValue) []*storage.WorkFlow {
	if m.conn == nil {
		return nil
	}
	if !checkContext(ctx) {
		return nil
	}
	sqlString := "SELECT * FROM WorkFlow WHERE OrgID = ? "
	var args = []any{ctx.OrgID}
	if workFlowCode != "" {
		sqlString += " AND WorkFlowCode = ? "
		args = append(args, workFlowCode)
	}
	if workFlowID != "" {
		sqlString += " AND WorkFlowID = ? "
		args = append(args, workFlowID)
	}
	if useStatus == 0 {
		sqlString += " AND IsUse = 0 "
	} else if useStatus == 1 {
		sqlString += " AND IsUse = 1 "
	}
	res := m.conn.QueryRows(sqlString, args...)
	ret, _ := godb.QueryResultToStructPtrArray[storage.WorkFlow](res)
	return ret
}

func (m *sqlStroage) WorkNodeQueryByID(workDetailID string) (*storage.WorkNode, error) {
	if m.conn == nil {
		return nil, sqlConnErr
	}
	sqlString := workNodeQuerySQL() + " WHERE B.WorkDetailID = ?"
	res := m.conn.QueryRows(sqlString, workDetailID)
	return godb.QueryResultRowToStructPtr[storage.WorkNode](res)
}

func (m *sqlStroage) WorkNodeCompleteAndReturnNextWorkNodeID(workNode *storage.WorkNode, isPass bool, memo string, ctx *public.ContextValue) (string, error) {
	if m.conn == nil {
		return "", sqlConnErr
	}
	if !checkContext(ctx) {
		return "", ctxNullErr
	}
	sqlString := "SELECT * FROM FlowNode WHERE NodeID = ?"
	flowNode := m.conn.QueryRows(sqlString, workNode.NodeID).GetMap()
	if len(flowNode) < 1 {
		return "", errors.New("流程节点不存在")
	}
	nextNodeID := conv.ToString(flowNode["ChildID"])
	if conv.ToInt(flowNode["NodeType"]) == storage.NodeTypeRoute {
		if isPass {
			nextNodeID = conv.ToString(flowNode["RouteSuccID"])
		} else {
			nextNodeID = conv.ToString(flowNode["RouteFailID"])
		}
	}
	if nextNodeID != "" {
		sqlString = "SELECT WorkDetailID FROM WorkDetail WHERE WorkID = ? AND NodeID = ? AND WorkDetailStatus = ?"
		detailID := m.conn.QueryRows(sqlString, workNode.WorkID, nextNodeID, storage.WorkDetailStatusSleep).Get("WorkDetailID")
		nextNodeID = conv.ToString(detailID)
	}
	return nextNodeID, m.conn.Transaction(func(tx godb.TxSQL) error {
		status := storage.WorkDetailStatusEnd
		if !isPass {
			status = storage.WorkDetailStatusStop
		}
		sqlString = "UPDATE WorkDetail SET WorkDetailMemo = ?,WorkDetailStatus = ?,UpdateUser = ?,UpdateTime = ?,RouteID = ? WHERE WorkDetailID = ? "
		err := tx.Exec(sqlString, memo, status, ctx.UserName, timeNow(), workNode.RouteID, workNode.WorkDetailID).Error()
		if err != nil {
			return err
		}
		if isPass && nextNodeID != "" {
			sqlString = "UPDATE WorkDetail SET WorkDetailStatus = ?,ParentNodeID = ? WHERE WorkDetailID = ? AND WorkDetailStatus = ? "
			return tx.Exec(sqlString, storage.WorkDetailStatusActive, workNode.NodeID, nextNodeID, storage.WorkDetailStatusSleep).Error()
		}
		if !isPass || workNode.NodeType == storage.NodeTypeEnd {
			// 如果是结束节点工作标记结束
			workStatus := storage.WorkStatusEnd
			if !isPass {
				workStatus = storage.WorkStatusStop
			}
			sqlString = "UPDATE Work SET WorkStatus = ?,WorkEndMemo = ? WHERE WorkID = ?"
			return tx.Exec(sqlString, workStatus, memo, workNode.WorkID).Error()
		}
		return nil
	})
}

func (m *sqlStroage) FlowNodeQuery(nodeID string) (storage.Node, error) {
	if m.conn == nil {
		return storage.Node{}, sqlConnErr
	}
	sqlString := "SELECT * FROM FlowNode WHERE NodeID = ?"
	res := m.conn.QueryRows(sqlString, nodeID)
	return godb.QueryResultRowToStruct[storage.Node](res)
}

func (m *sqlStroage) ActiveWorkNodeQueryByWorkID(workID string) (*storage.WorkNode, error) {
	if m.conn == nil {
		return nil, sqlConnErr
	}
	sqlString := workNodeQuerySQL() + " WHERE A.WorkID = ? AND B.WorkDetailStatus = ? "
	res := m.conn.QueryRows(sqlString, workID, storage.WorkDetailStatusActive)
	return godb.QueryResultRowToStructPtr[storage.WorkNode](res)
}

func (m *sqlStroage) FlowNodeUpdate(node storage.Node, routeNodes []storage.RouteNode, ctx *public.ContextValue) error {
	if m.conn == nil {
		return sqlConnErr
	}
	if !checkContext(ctx) {
		return ctxNullErr
	}
	return m.conn.Transaction(func(tx godb.TxSQL) error {
		nodeInsertSQL := "UPDATE FlowNode SET NodeName = ?,UserGroupID = ?, RouteID = ?, UpdateUser = ?, UpdateTime = ?,UpdateUserID = ? WHERE NodeID = ? "
		err := tx.Exec(nodeInsertSQL, node.NodeName, node.UserGroupID, node.RouteID, ctx.UserName, timeNow(), ctx.UserID, node.NodeID).Error()
		if err != nil {
			return err
		}
		if len(routeNodes) > 0 {
			routeNodeInsertSQL := "INSERT INTO RouteNode(TopID, RouteID, ParentID, Formula, ActType) VALUES (?, ?, ?, ?, ?)"
			for _, v := range routeNodes {
				err := tx.Exec(routeNodeInsertSQL, v.TopID, v.RouteID, v.ParentID, v.Formula, v.ActType).Error()
				if err != nil {
					return err
				}
			}
		}
		return nil
	})
}

func (m *sqlStroage) EnableWorkFlow(workFlowID string, ctx *public.ContextValue) error {
	if m.conn == nil {
		return sqlConnErr
	}
	if !checkContext(ctx) {
		return ctxNullErr
	}
	return m.conn.Transaction(func(tx godb.TxSQL) error {
		sqlString := "SELECT WorkFlowCode FROM WorkFlow WHERE WorkFlowID = ?"
		workFlowCodeV := tx.QueryRows(sqlString, workFlowID).Get("WorkFlowCode")
		workFlowCode := conv.ToString(workFlowCodeV)
		if workFlowCode == "" {
			return errors.New("工作流程信息获取失败")
		}
		sqlString = "UPDATE WorkFlow SET IsUse = 0,UpdateUser = ?,UpdateUserID = ?,UpdateTime = ? WHERE WorkFlowID != ? AND WorkFlowCode = ?"
		err := tx.Exec(sqlString, ctx.UserName, ctx.UserID, timeNow(), workFlowID, workFlowCode).Error()
		if err != nil {
			return err
		}
		sqlString = "UPDATE WorkFlow SET IsUse = 1,UpdateUser = ?,UpdateUserID = ?,UpdateTime = ? WHERE WorkFlowID = ? "
		return tx.Exec(sqlString, ctx.UserName, ctx.UserID, timeNow(), workFlowID).Error()
	})
}

func (m *sqlStroage) DisableWorkFlow(workFlowID string, ctx *public.ContextValue) error {
	if m.conn == nil {
		return sqlConnErr
	}
	if !checkContext(ctx) {
		return ctxNullErr
	}
	sqlString := "UPDATE WorkFlow SET IsUse = 0,UpdateUser = ?,UpdateUserID = ?,UpdateTime = ? WHERE WorkFlowID = ?"
	return m.conn.Exec(sqlString, ctx.UserName, ctx.UserID, timeNow(), workFlowID).Error()
}

func (m *sqlStroage) FlowDel(flowID string, ctx *public.ContextValue) error {
	if m.conn == nil {
		return sqlConnErr
	}
	if !checkContext(ctx) {
		return ctxNullErr
	}
	sqlString := "SELECT 1 FROM WorkFlow WHERE FlowID = ? "
	leng := m.conn.QueryRows(sqlString, flowID).Length()
	if leng > 0 {
		return errors.New("流程已被使用无法删除")
	}
	sqlString = "DELETE FROM FlowNode WHERE FlowNode.TopNodeID = ? "
	err := m.conn.Exec(sqlString, flowID).Error()
	return err
}

func (m *sqlStroage) WorkNodes(workID string, ctx *public.ContextValue) ([]*storage.WorkNode, error) {
	if m.conn == nil {
		return nil, sqlConnErr
	}
	sqlString := workNodeQuerySQL() + " WHERE B.WorkID = ? "
	res := m.conn.QueryRows(sqlString, workID)
	return godb.QueryResultToStructPtrArray[storage.WorkNode](res)
}

func (m *sqlStroage) WaitDoWorkList(workFlowCode string, userID string, ctx *public.ContextValue) ([]storage.WorkNode, error) {
	if m.conn == nil {
		return nil, sqlConnErr
	}
	if !checkContext(ctx) {
		return nil, ctxNullErr
	}
	sqlString := workNodeQuerySQL()
	sqlString += " WHERE B.WorkDetailStatus = 1 AND C.UserGroupID IN (SELECT UserGroupID FROM UserGroupUsers WHERE UserID = ? GROUP BY UserGroupID) AND A.OrgID = ? "
	var args = []any{userID, ctx.OrgID}
	if workFlowCode != "" {
		sqlString += " AND A.WorkFlowCode = ? "
		args = append(args, workFlowCode)
	}
	res := m.conn.QueryRows(sqlString+" ORDER BY A.CreateTime", args...)
	return godb.QueryResultToStructArray[storage.WorkNode](res)
}

func (m *sqlStroage) UserGroupList(ctx *public.ContextValue) ([]string, error) {
	if m.conn == nil {
		return nil, sqlConnErr
	}
	sqlString := "SELECT UserGroupID,UserGroupName FROM UserGroupUsers GROUP BY UserGroupID,UserGroupName "
	it := m.conn.QueryRows(sqlString).Iterator()
	var ret []string
	for it.HasNext() {
		data, err := it.Next()
		if err != nil {
			break
		}
		gid := common.ToString(data["UserGroupID"])
		gname := common.ToString(data["UserGroupName"])
		ret = append(ret, fmt.Sprintf("%s,%s", gid, gname))
	}
	return ret, nil
}

func (m *sqlStroage) UserGroupUserIDQuery(userGroupName string, ctx *public.ContextValue) ([]string, error) {
	if m.conn == nil {
		return nil, sqlConnErr
	}
	sqlString := "SELECT UserID FROM UserGroupUsers WHERE UserGroupName = ?"
	res := m.conn.QueryRows(sqlString, userGroupName)
	return godb.QueryResultFieldToBaseTypeArray[string](res, "UserID")
}

func (m *sqlStroage) UserGroupAddUserID(userGroupName string, userID string, ctx *public.ContextValue) error {
	if m.conn == nil {
		return sqlConnErr
	}
	if !checkContext(ctx) {
		return ctxNullErr
	}
	sqlString := "SELECT UserGroupID FROM UserGroupUsers WHERE UserGroupName = ? "
	groupIDV := m.conn.QueryRows(sqlString, userGroupName).Get("UserGroupID")
	groupID := conv.ToString(groupIDV)
	if groupID != "" {
		sqlString = "SELECT UserGroupUserID FROM UserGroupUsers WHERE UserGroupID = ? AND UserID = ? "
		userGroupUserID := m.conn.QueryRows(sqlString, groupID, userID).Get("UserGroupUserID")
		if conv.ToString(userGroupUserID) != "" { //已经存在直接返回
			return nil
		}
	} else {
		groupID = public.IDGenIntString()
	}
	sqlString = "INSERT INTO UserGroupUsers(UserGroupUserID, UserGroupID, UserGroupName, UserID, UpdateUserID, UpdateUser, UpdateTime) values (?, ?, ?, ?, ?, ?, ?)"
	err := m.conn.Exec(sqlString, public.IDGen(), groupID, userGroupName, userID, ctx.UserID, ctx.UserName, timeNow()).Error()
	if err != nil {
		return err
	}
	return nil
}

func (m *sqlStroage) UserGroupDelUserID(userGroupName string, userID string, ctx *public.ContextValue) error {
	if m.conn == nil {
		return sqlConnErr
	}
	sqlString := "SELECT UserGroupID FROM UserGroupUsers WHERE UserGroupName = ? "
	groupIDV := m.conn.QueryRows(sqlString, userGroupName).Get("UserGroupID")
	groupID := conv.ToString(groupIDV)
	if groupID == "" {
		return nil
	}
	sqlString = "SELECT UserGroupUserID FROM UserGroupUsers WHERE UserGroupID = ? AND UserID = ? "
	userGroupUserIDV := m.conn.QueryRows(sqlString, groupID, userID).Get("UserGroupUserID")
	userGroupUserID := conv.ToString(userGroupUserIDV)
	if userGroupUserID == "" { //不存在直接返回
		return nil
	}
	sqlString = "DELETE FROM UserGroupUsers WHERE UserGroupUserID = ?"
	err := m.conn.Exec(sqlString, userGroupUserID).Error()
	if err != nil {
		return err
	}
	return nil
}

func (m *sqlStroage) WorkQueryByID(workID string, ctx *public.ContextValue) (*storage.Work, error) {
	if m.conn == nil {
		return nil, sqlConnErr
	}
	sqlString := "SELECT * FROM Work WHERE WorkID = ? "
	res := m.conn.QueryRows(sqlString, workID)
	if res.Length() < 1 {
		return nil, nil
	}
	return godb.QueryResultRowToStructPtr[storage.Work](res)
}

func (m *sqlStroage) NodeUserGroupCheckUser(nodeID string, userID string, ctx *public.ContextValue) bool {
	if m.conn == nil {
		return false
	}
	sqlString := "SELECT B.UserGroupUserID FROM FlowNode A INNER JOIN UserGroupUsers B ON A.UserGroupID = B.UserGroupID WHERE A.NodeID = ? AND B.UserID = ?"
	idV := m.conn.QueryRows(sqlString, nodeID, userID).Get("UserGroupUserID")
	return conv.ToString(idV) != ""
}

func timeNow() string {
	return time.Now().Format("2006-01-02 15:04:05")
}

func workNodeQuerySQL() string {
	return `
SELECT A.WorkID,A.WorkFlowID,A.WorkFlowCode,A.WorkName,A.WorkStatus,A.WorkEndMemo,B.WorkDetailID,B.WorkDetailStatus,
       C.NodeID,C.NodeType,C.NodeName,B.RouteID,A.ExtendVal1,A.ExtendVal2,A.ExtendVal3,A.ExtendVal4,A.ExtendVal5,
       A.Data,C.RouteSuccID,C.RouteFailID,C.ChildID,C.RouteID as SRouteID
FROM Work A
    INNER JOIN WorkDetail B ON A.WorkID = B.WorkID
    INNER JOIN FlowNode C ON B.NodeID = C.NodeID
`
}
